Solved

Select from DB

Posted on 2013-11-27
32
229 Views
Last Modified: 2014-01-14
Hi i need to select from mysql all rows where it have Pair Stock # and after it select all rows where Pair Stock # = Stock #. This is one table. I know how to make it using multiple query but i want to make it as one.
CREATE TABLE IF NOT EXISTS `rapnetdatabase` (
  `Seller` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `RapNet Seller Code` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Shape` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Weight` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Color` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Fancy Color` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Fancy Intensity` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Fancy Overtone` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Clarity` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Cut Grade` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Polish` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Symmetry` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Fluorescence` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Measurements` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Lab` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Cert #` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Stock #` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Treatment` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `RapNet Price` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `RapNet Discount Price` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Depth %` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Table %` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Girdle` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Culet` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Comment` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `City` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `State` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Country` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Is Matched Pair Separable` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Pair Stock #` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Parcel number of stones` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Certificate URL` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `RapNet Lot #` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Date` varchar(255) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window

0
Comment
Question by:umaxim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 13
  • +1
32 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682132
Can you please clarify your requirement?

I understand the "select all rows where Pair Stock # = Stock #" part.

However, the "where it have Pair Stock #" requirement isn't clear. Did you want all rows where this column is non-empty?
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682140
The resulting query is probably going to look something like this:

select * from rapnetdatabase
where `Pair Stock #` = `Stock #`
or ???

Open in new window


Does the order matter in which the rows are returned? I ask because of the way you say "... and after it ..."
0
 
LVL 1

Author Comment

by:umaxim
ID: 39682164
yes i need where `Pair Stock #` = `Stock #` and it should not be empty
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682166
Then perhaps:
select * from rapnetdatabase
where `Pair Stock #` = `Stock #`
and `Pair Stock #` <> ''

Open in new window

0
 
LVL 1

Author Comment

by:umaxim
ID: 39682168
in Pair Stock it show subcategory of stock number. I try to make a query which will group for me stock number and pair stock item together.

It is every time 2 items and i want to show them together.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682178
Can you please provide an example of what the data looks like and what result you want?
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682183
It sounds like you want the two column values appended. It can be done like this:
select concat(`Stock #`, `Pair Stock #`) as combined_stock_num, *
from rapnetdatabase
where `Pair Stock #` = `Stock #`
and `Pair Stock #` <> ''

Open in new window

You can put a space character between the two values like this:
select concat(`Stock #`, ' ', `Pair Stock #`) as combined_stock_num, *
from rapnetdatabase
where `Pair Stock #` = `Stock #`
and `Pair Stock #` <> ''

Open in new window

0
 
LVL 1

Author Comment

by:umaxim
ID: 39682191
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from rapnetdatabase where `Pair Stock #` = `Stock #` LIMIT 0, 30' at line 1
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682205
I replicated your error by testing the query, and adding the table name like this fixed it:
select concat(`Stock #`, ' ', `Pair Stock #`) as combined_stock_num, rapnetdatabase.*
from rapnetdatabase
where `Pair Stock #` = `Stock #`
and `Pair Stock #` <> ''

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39682211
@umaxim, when you show us an error message it is very helpful for you to also show us the fully resolved query string that generated the error message!  We are experts but not mind readers and we can help the most when we have all of the information. Since LIMIT 0,30 was not part of the recommended query, we have to assume that you made some changes to the query string.  Perhaps we can help if we can see what cropped up in those changes.
0
 
LVL 1

Author Comment

by:umaxim
ID: 39682214
no it show me different information.

i will try to explain one more time.

I have pair diamonds.

So in Pair Stock # it have id of similar diamond.

In Stock # we have unique id.


So i need to take this database and show

Diamond and after it i need to show it's pair diamond.


so for example i have diamond with Pair Stock # = 2233
so next diamond need to show where Stock # = 2233

it should group them together so i can latter show people pair diamonds.

I can do it like

$sql = select * from rapnetdatabase;

and second query select * from rapnetdatabase where `Stock #` = $sql['Pair Stock #']

but it will make me too many query. I want to make just one.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682224
Ok, so is the data linked both directions like this:

Stock # 123
Pair Stock # 124
Stock # 124
Pair Stock # 123

Or just one direction like this:

Stock # 123
Pair Stock # 124
Stock # 124
Pair Stock #
0
 
LVL 1

Author Comment

by:umaxim
ID: 39682228
just one direction

Pair Stock # 124 -> Stock # 124  

Pair Stock # 127 -> Stock # 127

Pair Stock # 150 -> Stock # 150
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682237
Ok, well if the pair stock # is null when it's not populated, then this might work for you:
select coalesce(`Pair Stock #`,`Stock #`) as pair_order_number, rapnetdatabase.*
from rapnetdatabase
order by pair_order_number

Open in new window

0
 
LVL 1

Author Comment

by:umaxim
ID: 39682244
it is show me all rows where `Pair Stock #`,`Stock #` are empty
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682253
This seems to work for me:
SELECT coalesce(r.`Pair Stock #`,r.`Stock #`) as pair_order_number, r.*
 FROM rapnetdatabase  as r 
where r.`Pair Stock #` is not null
or exists (select 1 from rapnetdatabase  r2 
  where r2.`Pair Stock #` = r.`Stock #`)
order by pair_order_number

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682268
Fixed my latest query; please give it a try.
0
 
LVL 1

Author Comment

by:umaxim
ID: 39682304
it is show me just pair number but it is not group them
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682329
What do you mean group them? Do you mean SQL grouping? It should be ordering by the pair number. Can you show me an example of what you'd like to see?
0
 
LVL 1

Author Comment

by:umaxim
ID: 39682349
ok i make this query and it still do not show me write order

i need it show me

1. diamond - Pair Stock # 124
2. diamond - Stock # 124

3. diamond - Pair Stock # 127
4. diamond - Stock # 127

and .......

I need it to order one by one.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682358
How about this:
SELECT coalesce(r.`Pair Stock #`,r.`Stock #`) as pair_order_number, r.*
 FROM rapnetdatabase  as r 
where r.`Pair Stock #` is not null
or exists (select 1 from rapnetdatabase  r2 
  where r2.`Pair Stock #` = r.`Stock #`)
order by pair_order_number, r.`Pair Stock #` DESC

Open in new window

If that's not what you're after, please post the results it gives, so I can compare it with what you requested in your latest post.
0
 
LVL 1

Author Comment

by:umaxim
ID: 39682381
ok it is working now but can i filter everything which is not found his pair. So for example if i have number in pair said 123 but i do not have any item which pair with it it is just skip it
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682435
Do you mean that the `Pair Stock #` is not null but there doesn't exist a record with that Stock #?
0
 
LVL 1

Author Comment

by:umaxim
ID: 39682554
i mean that if for example if pair stock # empty or if i do not have product which `pair stock #` not found in `stock #` for example some one delete it or it was error.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682567
This seems to work for me:
SELECT coalesce(r.`Pair Stock #`,r.`Stock #`) as pair_order_number, r.*
 FROM rapnetdatabase  as r 
where (r.`Pair Stock #` is not null
and exists (select 1 from rapnetdatabase r3 
  where r3.`Stock #` = r.`Pair Stock #`)
)
or exists (select 1 from rapnetdatabase  r2 
  where r2.`Pair Stock #` = r.`Stock #`)
order by pair_order_number, r.`Pair Stock #` DESC

Open in new window

0
 
LVL 1

Author Comment

by:umaxim
ID: 39682574
it is do not find any results
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682578
Can you please check your data manually to ensure you actually have some pairs there. If there are, check that the join between the Stock # and Pair Stock # actually works from an sql.
0
 
LVL 1

Author Comment

by:umaxim
ID: 39682585
it is showed me pair in previews query which you gave me before. But the problem was it showed also rows which did not have pair.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39682603
Perhaps you can post a file of sample data? My latest query worked with test data that I set up, so I would need to know how your data differs to work out what's wrong.
0
 
LVL 1

Author Comment

by:umaxim
ID: 39682743
this is database
rapnetdatabase.sql
0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 39684271
Thanks for sharing the data; that helps immensely. I can see now there are no nulls (just empty values) so coalesce is not the correct function to use, and the query logic also needs updating. This appeared to work:
SELECT r.* , if( r.`Stock #` > r.`Pair Stock #`
AND r2.`Pair Stock #` <> '', r.`Stock #` , r2.`Stock #` ) AS Pair_Order
FROM rapnetdatabase AS r
JOIN rapnetdatabase AS r2 ON ( r2.`Stock #` = r.`Pair Stock #`
OR r.`Stock #` = r2.`Pair Stock #` )
WHERE (
r.`Pair Stock #` <> ''
OR r2.`Pair Stock #` <> ''
)
ORDER BY Pair_Order

Open in new window


Note that the query is pretty slow to run, even with indexes. To speed it up, you could add a Pair_Order column and populate it with the Pair_Order value. As new records are added or removed from the table, that Pair_Order value would need to be updated though. A better (more efficient to work with) way of managing pairs would be through a separate table though.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39685699
just to be clear....

can you have  

stock    pair stock
1           2
2           3
3        
11        12
12
13        12
....


what should the output be?

1
2
3
11
12
13
12

or just
1
2
3
11
12
13?


or something else?

and if Stock# was the same as 'pair stock #' then that should (in general be ignored , unless its a child of a prior stock/pair stock?
0

Featured Post

Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question