Solved

Select from DB

Posted on 2013-11-27
32
223 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
  • 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
 
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 108

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Execution Plan 3 28
Creating a slider 12 34
Duplicating MySQL Table columns 5 10
two tables one button 11 10
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now