select pname, cname
from
parent table a,
child_table b
where a.pid = b.pid
Except! Instead of getting the results in the form of:
Ben ben_Child1
Ben ben_Child2
Ben ben_Child3
...
I would like them in
Ben ben_Child1 ben_Child2
Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday) 1 1 a b c d e m 1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using where (media.mediaTyple like 'thumbnail' or media.mediaType is null) but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
I don't know if it's Friday or what, but I can't for the life of me come up with an easy way to do this:
I have 3 tables I want to join:
Sale Table: Sale_No Cus_No Sale_Qty 1 Joe01 250
Order Table: Ord_No Sale_No Order_Qty ShipToCode 1 1 20 DestA 2 1 20 DestA 3 1 20 DestA 4 1 20 DestB 5 1 20 DestB
ShipTo Table:
Cus_No ShipToCode ShipToName Joe01 DestA Philadelphia Joe01 DestB Chicago Bob01 DestA Boston
A sale for say 100 tons would have 5 orders (each for 20 tons) associated with it by Sale_No. Each of those orders can go to a different ShipTo destination. Since only the ShipTo Code is stored in the Orders table, I need to get the ShipToName. However, As demonstrated in the example table above, the key in the ShipTo table is both Cus_No AND ShipToCode.
I want a list of Sales and Orders, which is an inner join on Sale_No, piece of cake. However, I then need to use the ShipTo table to go from the ShipToCode to the ShipToName. Unfortunately, Cus_No is not in the Orders table, it is back in the Sales table (proper normalization is a pain sometimes).
What I came up with is this, but is this correct?:
FROM Sales INNER JOIN Orders ON Sales.sale_no = Orders.sale_no INNER JOIN ShipTo ON Orders.ShipToCode = ShipTo.ShipToCode AND Sales.cus_no = ShipTo.cus_no
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
Which is of messages in a mailbox system, the MessageNum is my primary key, MailboxNum indicates which mailbox it is for and State indicates whether it is 'New' (value = 1) or 'Saved' (value = 0).
What I want to do is write a query to obtain a list of mailboxes, along with how many New and how many Saved messages they have, producing a result table like this:
My problem is I cannot seem to get my SQL right, so far I've got:
PHP Code:
SELECT NewQuery.MailboxNum, NewQuery.NewCount, SavedQuery.SavedCount FROM (SELECT MailboxNum, COUNT(1) AS NewCount FROM VMailMessages WHERE (State = '1') GROUP BY MailboxNum) NewQuery FULL OUTER JOIN (SELECT MailboxNum, COUNT(1) AS SavedCount FROM VMailMessages WHERE (State = '0') GROUP BY MailboxNum) SavedQuery ON NewQuery.MailboxNum = SavedQuery.MailboxNum
WHich works only if the mailbox has New messages as well as Saved messages. For mailboxes with only Saved messages, the count appears but, the MailboxNum is NULL. The opposite occurs if I change my SELECT clause to ask for SavedQuery.MailboxNum, but I really want both.
I feel like this is an easy question, but I can't describe it well enough to find the answer I need by searching. Anyway, in my spare time (I'm definitely not a SQL Query pro) I'm putting together a small app for our local Little League to help with scheduling.
I have 2 tables I need to join:
T1 = Schedule has the following fields: ID hTeamID (ID of Home team) vTeamID (ID of Vistor team) Time Date
T2 = Teams ID Name Other info...
I want to be able to do a SELECT statement on the schedule table and JOIN the team names for both home and visitor. I've tried a bunch of different ways but keep getting errors. I can think of 100's of reasons to join the same table more than once, but I still don't know how to and can't find the answer online.
I have listed two tables table 1 has some data. I have to update table 2 [reports] column from table 1 [reports] using self join..I should get as table 2 after updating
In a single table I have 2 columns. Date | Number2006/09/01 - 2352006/09/03 - 2452009/08/01 - 230 I want to write a query that will return the AVG number between two dates. I am assuming this will require a JOIN but I'm having problems implementing my solution. I think about it, it's probably not a join but a sub query...I was trying the following: SELECT Date, AVG(Number) as AVERAGE_NUMBER FROM test.Table WHERE ( Date>='09/01/2006' AND DATE<='09/04/2006' ) But I keep getting date is invalid in the select list because it is not contained in either an aggregate funtion or a group by clause. Thanks in advance for your help.
I've got a simple ( I think) question on views. I've got a view that has a table join in it. With this view, we want to be able to perform updates, inserts, and deletes. At this time we can do the updates and inserts, but not deletes. I've checked the permissions and the users have SELECT, INSERT, UPDATE, and DELETE. Am I missing something or are deletes just not possible in a view with a join?
CREATE VIEW update_bd_view AS select D.BD_ID, D.BD_DESC, T.BT_TYPE_TID, T.BT_TYPE_FID, T.BT_JOB_FID FROM BILLING_DESC D JOIN BILLING_TIME T ON D.BD_ID=T.BT_ID GO
Please see the attached word file. It has screen shots of the query I am running. My first query shows the result where i have M.ReservationID = MA.MeetingID and it counts NoofRSVP (# of times the query runs). I have to modify first query in such a way that it returns records from eCDReservations table even if there is no matching MeetingID in MeetingAttendees table (means Null, see the result of 2nd query in attached file). So in my result for that case NoofRSVP column should show either Null or 0.
Hi,We are in the process of buying a new server to run mssql. Howeverbefore this as a tempory fix to using a msaccess backend i believethrough odbc i need to address the following issue:SELECT ai.entry_date as CallTime,ai.agent_login as AgentsLogin,ai.campaign as MarketingCampaign,ai.agent_input2 as ProductsSold,ai.first_name as Cust_FirstName,ai.last_name as Cust_LastName,ai.agent_input1 as Cust_PersonalNumber,ai.street_address as Cust_AddressStreet,ai.city as Cust_AddressCity,ai.state as Cust_AddressState,ai.zip as Cust_AddressZIP,rec.file_name as AgreementRecordingFileFROM agent_input ai, leads l, recordings recWHERE ai.whole_phone_number = l.whole_phone_number ANDl.call_status = 1110 ANDrec.whole_phone_number = l.whole_phone_number ANDrec.last_name = l.last_name ANDrec.agent = ai.agent_login ANDrec.campaign = l.campaign ANDlast_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#ORDER BY ai.agent_login, ai.entry_dateI want to make the recordings entry optional so the same results comeout whether it matches a recording or not. If it does i want it topopulate the AgreementRecordingFile column above, if not just put a ''as you would with '' as AgreementRecordFile.Does anyone know how you can do this, in a access based database systemusing SQL through i believe ODBC?ThanksDavid
Two tables:FruitfruitID, fruitNameBasketbuyerID, fruitID(ie. we can see which buyer has what fruit in their basket)I simply want to display all available fruit and whether or not it'sin a specific persons' basket.SELECT Fruit.fruitID, Fruit.fruitName, IsNull(buyerID, 0)FROM Fruit INNER JOIN Basket ON Fruit.fruitID = Basket.fruitIDWHERE Basket.buyerID = 12but this just gives me what's in buyer 12s' basket.What am I doing wrong? Am I a basket case...
This simple query is designed to show all members with the same first name as other members. The result I got shows duplicates of existing members an inconsistent number of times even though I specified not to show duplicates with WHERE m1.MemberID <> m2.MemberID
2 Scharenguivil Rodney 1958-06-24 00:00:00.000 2 Scharenguivil Rodney 1958-06-24 00:00:00.000 2 Scharenguivil Rodney 1958-06-24 00:00:00.000 5 O'Grady Patrick 1975-09-23 00:00:00.000 7 Greenfield Lynne 1955-07-26 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000
Any help in explaining where I have gone wrong here would be greatly appreciated.
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
The select command below will output one patient’s information in 1 row:
Patient id Last name First name Address 1 OP Coverage Plan 1 OP Policy # 1 OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1, max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1, max(case when rn = 1 then src.policy_id end) as OP_Policy1,
I am trying to select a particular record from a table and for some reason it only returns 1 record when I know there are at least 6. I've tried Rtrim and left to make sure that I'm getting the exact string. The "MyID" field is varchar(32) and the "ExpireDate field is datetime and I have tried the below. Any ideas what could be wrong?
Select DISTINCT K.MyID, KP.[EXPIREDATE] --, K.ACTIVATIONKEY From [KEY] K INNER JOIN KEY_PRODUCT KP ON K.MyKey = KP.MyKey Where K.MyID = '013BEB73C2CF11D39F3600105A05264C'
I have a simple select statement that I am having a complex with. I am a C# developer trying to expand and gain a little more knowledge of SQL. I want to send a string to a SQLCommand catagory = 'news,alerts,events' and return a result set if it matches any term.
This is for a blog I am creating for a church and the catagory is like the tags you normally see. I could use something like:
SELECT GUID,CATAGORY,DESCRIPTION,TITLE,ETC FROM RSS_FEEDS WHERE CATAGORY LIKE 'news,alerts,events'
I can seperate the keywords with any character if needed.
WHERE (ITEM_CATEGORY LIKE '%news,events%')
This only returns ones that have all like what is there not the ones having news only
Hi I Have the following table SequenceNumber___TypeID8_________________IMG7_________________IMG6_________________IMG5_________________IMG4_________________IMG3_________________IMG2_________________FLP2_________________IMG I want to pull the data out in the following format, SequenceNumber___TypeID8_________________IMG2_________________FLP This basically shows the highest SequenceNumber of each TypeID, I've tried many different SQL queries but I can't seem to get it! Any ideas?
I am new to ASP, I come from a PowerBuilder background. I like the sqldatasource model, but I have a question. In PowerBuilder I could just write straight 'embeded' sql in the code.such as Select XFROM YWhere XYZ; So my question is this: I want to get the value out of the database and set a variable to it where I know a key field, what's the quickest way to do it? Thanks Dan
Ok, I do know SQL and have been using it for quite soem time. For some reason, it is giving me an error and I was wondering if someone could help.
Here is the few lines of interest
System.Data.SqlClient.SqlCommand command; command = new System.Data.SqlClient.SqlCommand(@"SELECT Password FROM User WHERE Username='" + user + "'", this.sqlConn);
dataReader = command.ExecuteReader();
This is the error I am coming up with.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'User'.
I have tried enclosing the Table name in quotes and removing the @. What am I doing wrong? Thanks!
This is the query I wrote, but it only selects the personel who have Workgroups and Access Groups assigned to them but I want to show all the employies and if they don't have Workgroups and Access Groups assigned to them then show empty cells on DBGrid... and I know the red part is the problem but I could not build up the logic to select the matching records and empty records at the same time... Thanks in advance
There is a stored procedure that updates a "sales" table with the current "sales representative" taken from the "customers" table. I'm changing this mess of cursors into a simple update, my first approach was just to do a select (instead of update) just to verify that the rows selected were the ones that really needed to be changed. The select is very simple but is returning a cartesian product instead of just the 200+ (aprox) rows. I would appreciate if someone took a quick look at this select and see what I'm missing :) (I'm including the code for the cursor as well as the code for the select)
CURSOR:
CREATE PROCEDURE [ReCast_Salesman] AS -- Recast salesman
Set @i = 0 Set @cnt = 0 Set @ucnt = 0 Set @icnt = 0 Set @UpdFlag = 'N'
Declare Customer_cur Cursor for Select site_code, cust_no, cust_sffx, sales_rep From Customer where Active_Flag = 'A' and sales_rep <> 'XXXX' Order by site_code, cust_no, cust_sffx For Read only
Open Customer_Cur
Fetch Next from Customer_Cur into @vsite_code, @vcustno, @vcustfx, @vsalrep
While @@FETCH_STATUS = 0 Begin
Declare sales_cur Cursor for Select site_code, cust_no, cust_sffx, salesrep From Sales Where site_code = @vsite_code and cust_no = @vcustno and cust_sffx = @vcustfx and salesrep <> @vsalrep Order by Site_code, cust_no, cust_sffx For Update
Open Sales_cur
Begin
Fetch Next from sales_cur into @ssite_code, @scustno, @scustfx, @Ssalrep
While (@@fetch_status = 0) Begin
--If @vsalrep <> @Ssalrep
-- Begin Update Sales set salesrep = @vsalrep Where current of Sales_cur
set @ucnt = @ucnt + 1 -- End
Fetch Next from sales_cur into @ssite_code, @scustno, @scustfx, @Ssalrep
End
FetchNext: deallocate sales_cur
Fetch Next from Customer_Cur into @vsite_code, @vcustno, @vcustfx, @vsalrep
End
End
set @vprodate = getdate() set @vpro = 'Recast Salesman ' set @vmesg = Str(@ucnt) + ' Records Changed'
use salesdatamart; Select A.site_code, A.cust_no, A.cust_sffx, A.salesrep, From Sales As A Inner Join Customer As B OnA.site_code = B.site_code AND A.cust_no = B.cust_no AND A.cust_sffx = B.cust_sffx AND A.salesrep != B.sales_rep AND B.sales_rep != 'XXXX' AND B.active_flag = 'A'
I have a select statement that is not finding a recordset that I know is there.
SQL_1 = "SELECT cust_id, firstname, lastname, email FROM tbl_customers WHERE email = '" & email & "'"
Works fine on most email addresses, but on email addresses where there is a period in the name it does not find the record. The email string is being submitted via a form. Before the SQL statement above I have the request from the form.
email = Request("email")
Like I said, this select works fine on most email addresses. Im sure this is a formatting issue.
If I manually type the select statement into SQL Query Analyzer it works also, but of couse I am manually typing the email address in there that contains the period in the email address.
I have verified that the request is pulling the correct email address with the period.
I've been struggling with this problem, hope someone can help. I'm using SQL Server 2005 Express. I have a simple database with 7 tables (m64,m67,m69,m71,m87) all have an identical design(column name, type). All I want to do is pull out one recordset (if it's in that table) from each of the tables.
This is what I have: "SELECT * FROM m64, m67, m69, m71, M87 WHERE P_N Like '" & Request.Querystring("P_N") & "'"
Hi,I feel so stupid to ask this question, but here it goesWhen I select a column from a table, if the column has a null value Iwant the select to return me a blank. I have done this before but Iforgot, some one help?select name, age from peoplein the above query the name can be null. I do not want to doselect @name=name....Thanks.
Hi,I cant figure out how to do this....for example:Select name from mytab order by col1could returnMikeDaveSueSimonPaulFredI would like to show the row number, like in the grid in query analyser. sothe orginal sort order is preservedi.e1 Mike2 Dave3 Sue4 Simon5 Paul6 FredAny ideas?
