Select from DB

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

LVL 1
umaximAsked:
Who is Participating?
 
Terry WoodsConnect With a Mentor IT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
umaximAuthor Commented:
yes i need where `Pair Stock #` = `Stock #` and it should not be empty
0
 
Terry WoodsIT GuruCommented:
Then perhaps:
select * from rapnetdatabase
where `Pair Stock #` = `Stock #`
and `Pair Stock #` <> ''

Open in new window

0
 
umaximAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
Can you please provide an example of what the data looks like and what result you want?
0
 
Terry WoodsIT GuruCommented:
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
 
umaximAuthor Commented:
#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
 
Terry WoodsIT GuruCommented:
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
 
Ray PaseurCommented:
@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
 
umaximAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
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
 
umaximAuthor Commented:
just one direction

Pair Stock # 124 -> Stock # 124  

Pair Stock # 127 -> Stock # 127

Pair Stock # 150 -> Stock # 150
0
 
Terry WoodsIT GuruCommented:
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
 
umaximAuthor Commented:
it is show me all rows where `Pair Stock #`,`Stock #` are empty
0
 
Terry WoodsIT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
Fixed my latest query; please give it a try.
0
 
umaximAuthor Commented:
it is show me just pair number but it is not group them
0
 
Terry WoodsIT GuruCommented:
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
 
umaximAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
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
 
umaximAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
Do you mean that the `Pair Stock #` is not null but there doesn't exist a record with that Stock #?
0
 
umaximAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
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
 
umaximAuthor Commented:
it is do not find any results
0
 
Terry WoodsIT GuruCommented:
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
 
umaximAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
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
 
umaximAuthor Commented:
this is database
rapnetdatabase.sql
0
 
LowfatspreadCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.