asked on

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

Terry Woods

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?

Terry Woods

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 ..."

umaxim

ASKER

yes i need where `Pair Stock #` = `Stock #` and it should not be empty

Terry Woods

Then perhaps:

select * from rapnetdatabase
where `Pair Stock #` = `Stock #`
and `Pair Stock #` <> ''

Open in new window

umaxim

ASKER

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.

Terry Woods

Can you please provide an example of what the data looks like and what result you want?

Terry Woods

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

umaxim

ASKER

#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

Terry Woods

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

Ray Paseur

@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.

umaxim

ASKER

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.

Terry Woods

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 #

umaxim

ASKER

just one direction

Pair Stock # 124 -> Stock # 124

Pair Stock # 127 -> Stock # 127

Pair Stock # 150 -> Stock # 150

Terry Woods

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

umaxim

ASKER

it is show me all rows where `Pair Stock #`,`Stock #` are empty

Terry Woods

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

Terry Woods

Fixed my latest query; please give it a try.

umaxim

ASKER

it is show me just pair number but it is not group them

Terry Woods

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?

umaxim

ASKER

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.

Terry Woods

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.

umaxim

ASKER

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

Terry Woods

Do you mean that the `Pair Stock #` is not null but there doesn't exist a record with that Stock #?

umaxim

ASKER

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.

Terry Woods

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

umaxim

ASKER

it is do not find any results

Terry Woods

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.

umaxim

ASKER

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.

Terry Woods

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.

umaxim

ASKER

this is database
rapnetdatabase.sql

ASKER CERTIFIED SOLUTION

Terry Woods

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

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?