Select query....

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Hebbs
Forum Commoner
Posts: 43
Joined: Mon Apr 22, 2002 9:34 pm
Location: Perth, Western Australia

Select query....

Post by Hebbs »

I am having trouble getting my head around a problem where I need to select from two tables where the values of col a and col b in both tables is equal.

The problem is compounded by the fact that both cols in both tables are non unique values and repeat often.

What I need is a WHERE condition something like:

"WHERE table1.cola,table1.colb = table2.cola,table2.colb"

Is this feasible?

Hebbs
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Re: Select query....

Post by protokol »

Hebbs wrote: "WHERE table1.cola,table1.colb = table2.cola,table2.colb"
Try this:

SELECT * FROM table1, table2 WHERE table1.cola = table2.cola AND table1.colb = table2.colb
Hebbs
Forum Commoner
Posts: 43
Joined: Mon Apr 22, 2002 9:34 pm
Location: Perth, Western Australia

Post by Hebbs »

OK then, nearly there.

Working along those lines, how do you structure the SELECT when you want to select * from tablea EXCEPT for those rows where table1.cola = table2.cola AND table1.colb = table2.colb.

Hebbs
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Code: Select all

SELECT field1, field2, field3 FROM tablea AS t1, tableb AS t2 WHERE t1.cola <> t2.cola AND t1.colb <> t2.colb
Mac
Hebbs
Forum Commoner
Posts: 43
Joined: Mon Apr 22, 2002 9:34 pm
Location: Perth, Western Australia

Post by Hebbs »

Still cant get it happenen.

Below is the changed code as suggested (in part) by Mac.

What Im trying to do is list all the fields of table dat_subs that do not appear in dat_reconciled but also meet a variety of other conditions.

The following times out.

Code: Select all

"SELECT dat_a_mainrequest.ID_Nr AS ID_Number,dat_subs.SubEnqNr AS Part,dat_subs.Result_Date AS ResultDate,dat_subs.Cost,type_rqst.RequestType,dat_subs.SvcProreqNum AS ProvRefNr,list_State.Statename AS StateName,
dat_subs.Cost_Centre AS CostCentre,dat_a_reconciled.ID_Nr,dat_a_reconciled.SubEnqNr
FROM dat_a_mainrequest,dat_subs,list_requester,list_state,type_rqst,dat_a_reconciled
WHERE (dat_subs.Result_Returned = 'Y' OR dat_subs.Result_Returned = 'Nil') 
AND dat_a_mainrequest.ID_Nr = dat_subs.ID_Nr 
AND ((dat_subs.ID_Nr <> dat_a_reconciled.ID_Nr)
AND (dat_subs.SubEnqNr <> dat_a_reconciled.SubEnqNr))
AND SvcProCheckType = '$company' 
AND dat_a_mainrequest.ReqType_ID <= 5 
AND dat_a_mainrequest.ReqType_ID = type_rqst.ReqType_ID 
AND Result_Date >= '$recdatefm' 
AND Result_Date <= '$recdateto' 
AND ((dat_subs.SubRqst_Type_ID != '') OR (Number != ''))
AND dat_a_mainrequest.RequestOffice_ID = list_requester.RequestOffice_ID 
AND list_requester.State_ID = list_State.State_id "
Regards

Hebbs
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Have you tried building up the query slowly, one condition at a time to see how far you can get that way?

Mac
User avatar
haagen
Forum Commoner
Posts: 79
Joined: Thu Jul 11, 2002 3:57 pm
Location: Sweden, Lund

Post by haagen »

Just a tip. If ju don't feel confident writing sql, try some query building tool. You can find these in many diffrent programs, the most common (on window are);

access, SQL Analyser, MSSQLQuery.

From these you can quite easily build your query.
Hebbs
Forum Commoner
Posts: 43
Joined: Mon Apr 22, 2002 9:34 pm
Location: Perth, Western Australia

Post by Hebbs »

Mac,

The query works fine until I added

Code: Select all

AND ((dat_subs.ID_Nr <> dat_a_reconciled.ID_Nr) 
AND (dat_subs.SubEnqNr <> dat_a_reconciled.SubEnqNr))
What is happening is that the query grabs the data from my major tables and eventually lists it into a page where each entry is reconciled against an account.

When reconciled the details are carried over into the reconciled table so I have a record of whats done and other bits.

Each entry has a numeric value

Code: Select all

dat_subs.ID_Nr
and a sub number (there can be many parts to each entry)

Code: Select all

dat_subs.SubEnqNr
.

Each time an account is reconciled its ID and sub number is duplicated in the reconciled table.

So I need to ba able to call all files from the first table that havent been reconciled and entered in the other.

I think that the statement you supplied is close but not quite there. I tried leaving of the parenthesis but just got heaps of duplicate records!

Yours in babbling hopfully!!!!

Hebbs
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

I think that you probably want to be looking into using a join:
http://www.mysql.com/doc/en/JOIN.html

I tend to write SQL statements by trial and error (more fun that way) but I think that it's a LEFT JOIN that you need, the statement below (cribbed from something I did a little while ago) takes a bunch of records from two tables where the section ID is not found in the pageinfo table.

Code: Select all

SELECT t2.ID AS ID, t2.section AS section, t2.sec_title AS title FROM section AS t2 LEFT JOIN pageinfo AS t1 ON t1.section=t2.ID WHERE ISNULL(t1.section)
I'm fairly sure that this is the way you want to be going but someone who works with these types of SQL queries more often may be able to give better guidance.

Mac
Hebbs
Forum Commoner
Posts: 43
Joined: Mon Apr 22, 2002 9:34 pm
Location: Perth, Western Australia

Post by Hebbs »

Woohoooooo!!!

Fixed it, finally.

Found that a variation of the LEFT JOIN did the trick.

Rather than use the IF NULL (It didnt quite bring the results I needed!) I used the following:

Code: Select all

SELECT * FROM tablea,tableb,
tablec LEFT JOIN tabled USING (col1,col2)
WHERE ...
This had the desired effect in returning values from tablec that didnt appear in tabled,

The only proviso is that the tables you are joining must both have identical column names (col1, col2) for this to work.

Appreciate everyones help and especially your persistance Mac.

Hebbs
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

SQL queries are definitely much easier to write when you can trial or error them on the actual data :) . Glad you got it sorted.

Mac
Post Reply